package com.clothing.customization.repository;

import com.clothing.customization.entity.CustomOrder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public interface CustomOrderRepository extends JpaRepository<CustomOrder, Long> {

    /**
     * 获取指定日期的订单统计数据
     */
    @Query(value = """
        SELECT 
            COUNT(*) as count,
            COALESCE(SUM(final_price), 0) as amount
        FROM tb_custom_order
        WHERE DATE(create_time) = DATE(:date)
    """, nativeQuery = true)
    Map<String, Object> getDailyOrderStats(@Param("date") Date date);

    /**
     * 获取指定时间范围内的订单统计数据
     */
    @Query(value = """
        SELECT 
            COUNT(*) as count,
            COALESCE(SUM(final_price), 0) as amount
        FROM tb_custom_order
        WHERE create_time BETWEEN :startDate AND :endDate
    """, nativeQuery = true)
    Map<String, Object> getOrderStatsBetween(
        @Param("startDate") Date startDate,
        @Param("endDate") Date endDate
    );

    /**
     * 获取指定时间范围内的品类分布数据
     */
    @Query(value = """
        SELECT 
            clothing_type as name,
            COUNT(*) as value
        FROM tb_custom_order
        WHERE create_time BETWEEN :startDate AND :endDate
        GROUP BY clothing_type
        ORDER BY value DESC
    """, nativeQuery = true)
    List<Map<String, Object>> getCategoryDistribution(
        @Param("startDate") Date startDate,
        @Param("endDate") Date endDate
    );

    /**
     * 获取指定时间范围内的地区分布数据
     */
    @Query(value = """
        SELECT 
            u.region as name,
            COUNT(*) as value
        FROM tb_custom_order o
        JOIN tb_user u ON o.user_id = u.id
        WHERE o.create_time BETWEEN :startDate AND :endDate
        GROUP BY u.region
        ORDER BY value DESC
    """, nativeQuery = true)
    List<Map<String, Object>> getRegionDistribution(
        @Param("startDate") Date startDate,
        @Param("endDate") Date endDate
    );

    /**
     * 获取指定时间范围内的渠道分布数据
     */
    @Query(value = """
        SELECT 
            channel as name,
            COUNT(*) as value
        FROM tb_custom_order
        WHERE create_time BETWEEN :startDate AND :endDate
        GROUP BY channel
        ORDER BY value DESC
    """, nativeQuery = true)
    List<Map<String, Object>> getChannelDistribution(
        @Param("startDate") Date startDate,
        @Param("endDate") Date endDate
    );

    /**
     * 获取指定时间范围内的订单明细数据
     */
    @Query(value = """
        SELECT 
            DATE(create_time) as date,
            COUNT(*) as orderCount,
            COUNT(CASE WHEN status = 'completed' THEN 1 END) as completedCount,
            COALESCE(SUM(final_price), 0) as totalAmount,
            (
                SELECT clothing_type
                FROM tb_custom_order o2
                WHERE DATE(o2.create_time) = DATE(o1.create_time)
                GROUP BY clothing_type
                ORDER BY COUNT(*) DESC
                LIMIT 1
            ) as categoryTop,
            (
                SELECT u.region
                FROM tb_custom_order o2
                JOIN tb_user u ON o2.user_id = u.id
                WHERE DATE(o2.create_time) = DATE(o1.create_time)
                GROUP BY u.region
                ORDER BY COUNT(*) DESC
                LIMIT 1
            ) as regionTop
        FROM tb_custom_order o1
        WHERE create_time BETWEEN :startDate AND :endDate
        GROUP BY DATE(create_time)
        ORDER BY date DESC
        LIMIT :offset, :pageSize
    """, nativeQuery = true)
    List<Map<String, Object>> getOrderDetails(
        @Param("startDate") Date startDate,
        @Param("endDate") Date endDate,
        @Param("offset") int offset,
        @Param("pageSize") int pageSize
    );

    /**
     * 统计指定时间范围内的订单总数
     */
    @Query(value = """
        SELECT COUNT(DISTINCT DATE(create_time))
        FROM tb_custom_order
        WHERE create_time BETWEEN :startDate AND :endDate
    """, nativeQuery = true)
    long countOrdersBetween(
        @Param("startDate") Date startDate,
        @Param("endDate") Date endDate
    );

    /**
     * 获取导出数据
     */
    @Query(value = """
        SELECT 
            DATE(create_time) as date,
            COUNT(*) as orderCount,
            COALESCE(SUM(final_price), 0) as totalAmount,
            COUNT(CASE WHEN status = 'completed' THEN 1 END) as completedCount,
            (
                SELECT clothing_type
                FROM tb_custom_order o2
                WHERE DATE(o2.create_time) = DATE(o1.create_time)
                GROUP BY clothing_type
                ORDER BY COUNT(*) DESC
                LIMIT 1
            ) as categoryTop,
            (
                SELECT u.region
                FROM tb_custom_order o2
                JOIN tb_user u ON o2.user_id = u.id
                WHERE DATE(o2.create_time) = DATE(o1.create_time)
                GROUP BY u.region
                ORDER BY COUNT(*) DESC
                LIMIT 1
            ) as regionTop
        FROM tb_custom_order o1
        WHERE create_time BETWEEN :startDate AND :endDate
        GROUP BY DATE(create_time)
        ORDER BY date DESC
    """, nativeQuery = true)
    List<Map<String, Object>> getOrderDetailsForExport(
        @Param("startDate") Date startDate,
        @Param("endDate") Date endDate
    );
} 